package classes;

import DBConnection.*;
import com.mysql.jdbc.ResultSetMetaData;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.regex.Pattern;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Ervin  James
 */
public class PatientManagement {
    
    public boolean AddPatient(Patient patient)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO patientlist (PatientLName, PatientFName, PatientMName, PatientAddress, PatientAge, PatientGender, PatientContactNo) VALUES(?,?,?,?,?,?,?)");

            //include parameters
            pstmt.setString(i++, patient.getLastName());
            pstmt.setString(i++, patient.getFirstName());
            pstmt.setString(i++, patient.getMiddleName());
            pstmt.setString(i++, patient.getAddress());
            pstmt.setString(i++, patient.getAge());
            pstmt.setString(i++, patient.getGender());
            pstmt.setString(i++, patient.getContactNo());
            
            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(Exception e)
            {
               e.printStackTrace();  
            }
            
         return result;
    }
    
    public boolean checkPatient(String lastName, String firstName, String middleName)
    {
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM patientlist WHERE PatientLName = ? AND PatientFName = ? AND PatientMName = ?");

            pstmt.setString(i++, lastName);
            pstmt.setString(i++, firstName);
            pstmt.setString(i++, middleName);

            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {
                result = true;
            }

            conn.close();
        }
        catch(SQLException e)
        {
           //e.printStackTrace();
        }

        return result;

    }
    
    public boolean checkPatientOverlap(String lastName, String firstName, String middleName,String id)
    {
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM patientlist WHERE PatientLName = ? AND PatientFName = ? AND PatientMName = ? AND PatientID<> ?");

            pstmt.setString(i++, lastName);
            pstmt.setString(i++, firstName);
            pstmt.setString(i++, middleName);
            pstmt.setString(i++, id);

            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {
                result = true;
            }

            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;

    }
    
    public Vector SearchPatient(int filter, String Keyword)
    {
        Vector patientList = new Vector<String>();
        String query = new String();
          
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;
            
            if(filter == 0)
                 query = "SELECT * FROM patientlist ORDER BY PatientID";
            else if(filter == 1) 
                query = "SELECT * FROM patientlist WHERE PatientFName RLike ? ORDER BY PatientLName";
            else if (filter == 2)
                query = "SELECT * FROM patientlist WHERE PatientLName RLike ? ORDER BY PatientLName";
            else if (filter == 3)
                query = "SELECT * FROM patientlist WHERE PatientAddress RLike ? ORDER BY PatientLName";
            else if (filter == 4)
                query = "SELECT * FROM patientlist WHERE PatientAge RLike ? ORDER BY PatientLName";
            else if (filter == 5)
                query = "SELECT * FROM patientlist WHERE PatientGender RLike ? ORDER BY PatientLName";
            
            
           
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setString(i++, Keyword);
            
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            int colNo = rsmd.getColumnCount();
                  
            while(rs.next())
            {
                Vector patient = new Vector();
                
                for(int j=0;j<colNo;j++){
                    patient.add(rs.getString(j+1));
                    }        
                patientList.add(patient);
     
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           // e.printStackTrace();
        }
        catch(Exception a)
        {
            
        }


        return patientList;
    }
    
    public void EditPatient(String lastName, String firstName, String middleName, String address, String age, String gender ,String contactNo, String patientNo)
    {
       
     try {
         ConnectionFactory myFactory = ConnectionFactory.getFactory();
         Connection conn = myFactory.getConnection();

         int i = 1;

        PreparedStatement pstmt = conn.prepareStatement("UPDATE patientlist SET PatientLName= ?, PatientFName= ?, PatientMName= ?, PatientAddress = ?, PatientAge = ?, PatientGender = ?, PatientContactNo = ? WHERE PatientID = ?");
        pstmt.setString(i++, lastName);
        pstmt.setString(i++, firstName);
        pstmt.setString(i++, middleName);
        pstmt.setString(i++, address);
        pstmt.setString(i++, age);
        pstmt.setString(i++, gender);
        pstmt.setString(i++, contactNo);
        pstmt.setString(i++, patientNo);
       
        pstmt.executeUpdate();


        conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }
        
    }
    
    public Patient viewPatient(String id)
    {
        Patient patient = new Patient();
        
        try {
         ConnectionFactory myFactory = ConnectionFactory.getFactory();
         Connection conn = myFactory.getConnection();

         int i = 1;

        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM patientlist WHERE PatientID = ? ORDER BY PatientID");
        pstmt.setString(i++, id);
        ResultSet rs = pstmt.executeQuery();


        while(rs.next())
        {
         patient.setPatientNo(rs.getString("PatientID"));   
         patient.setLastName(rs.getString("PatientLName"));
         patient.setFirstName(rs.getString("PatientFName"));
         patient.setMiddleName(rs.getString("PatientMName"));
         patient.setAddress(rs.getString("PatientAddress"));
         patient.setAge(rs.getString("PatientAge"));
         patient.setGender(rs.getString("PatientGender"));
         patient.setContactNo(rs.getString("PatientContactNo"));
        }

        conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }
        
        return patient;
    }
    
     public Vector ViewAllPatient()
    {
         Vector patientList = new Vector<String>();
          
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM patientlist ORDER BY PatientLName");
            ResultSet rs = pstmt.executeQuery();
            
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            int colNo = rsmd.getColumnCount();
         
            while(rs.next())
            {
                Vector patient = new Vector();
                
                for(int j=0;j<colNo;j++){
                    patient.add(rs.getString(j+1));
                    }        
                patientList.add(patient);
     
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return patientList;
    }
     
    public Vector FetchServices()
    {
        Vector serviceList = new Vector<String>();
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT ServiceName FROM servicecategory ORDER BY CategoryID");
            ResultSet rs = pstmt.executeQuery();
           
            
            
            while(rs.next())
            {
                serviceList.add(rs.getString("ServiceName"));
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return serviceList;
    }
    
     public Vector ViewAllServiceRendered(String patientid) {
        Vector serviceList = new Vector<String>();
          
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT ServiceID,(SELECT ServiceName from servicecategory WHERE servicecategory.CategoryID = servicerendered.CategoryID), Description, ToothNo, Date, Price FROM servicerendered WHERE PatientID = ? ORDER BY Date DESC");
            pstmt.setString(i++, patientid);
            ResultSet rs = pstmt.executeQuery();
            
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            int colNo = rsmd.getColumnCount();
         
            while(rs.next())
            {
                Vector service = new Vector();
                
                for(int j=0;j<colNo;j++){
                    service.add(rs.getString(j+1));
                    }        
               serviceList.add(service);
     
            }
           
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return serviceList;
    }
    
    public boolean AddServiceRendered(PatientDentalFile service)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO servicerendered (CategoryID, PatientID, Description, ToothNo, Date, Price) VALUES((SELECT CategoryID FROM servicecategory WHERE ServiceName = ?),?,?,?,?,?)");

            //include parameters
            pstmt.setString(i++, service.getCategoryName());
            pstmt.setString(i++, service.getPatientNo());
            pstmt.setString(i++, service.getDescription());
            pstmt.setString(i++, service.getToothNo());
            pstmt.setString(i++, service.getDate());
            pstmt.setString(i++, service.getPrice());
            
            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(Exception e)
            {
               e.printStackTrace();  
            }
            
         return result;
    }
   
    
    public PatientDentalFile ViewDentalRecord(String id)
    {
        PatientDentalFile record = new PatientDentalFile();
        
        try {
         ConnectionFactory myFactory = ConnectionFactory.getFactory();
         Connection conn = myFactory.getConnection();

        int i = 1;

        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM servicerendered WHERE ServiceID = ? ORDER BY PatientID");
        pstmt.setString(i++, id);
        ResultSet rs = pstmt.executeQuery();


        while(rs.next())
        {
         int j=1;
         PreparedStatement pstmt2 = conn.prepareStatement("SELECT ServiceName FROM servicecategory WHERE CategoryID = ?");
         pstmt2.setString(j++, rs.getString("CategoryID"));
         ResultSet rs2 = pstmt2.executeQuery();
         while(rs2.next())
            record.setCategoryName(rs2.getString("ServiceName"));
         record.setDescription(rs.getString("Description"));               
         record.setPrice(rs.getString("Price"));
         record.setToothNo(rs.getString("ToothNo"));
         record.setServiceID(rs.getString("ServiceID"));
        }

        conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }
        
        return record;
    }
    
    public boolean EditRecord(String category, String desc, String toothNo, String price, String id)
    {
        
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            

            int i = 1;
            int j = 1;
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Update servicerendered SET CategoryID = ?, Description = ?, ToothNo = ?, Price= ? WHERE ServiceID = ?");
            
            PreparedStatement pstmt2 = conn.prepareStatement("SELECT CategoryID from servicecategory WHERE ServiceName = ?");
            pstmt2.setString(j++, category);
            ResultSet rs = pstmt2.executeQuery();
            
            //include parameters
            while(rs.next())
                pstmt.setString(i++, rs.getString("CategoryID"));
            pstmt.setString(i++, desc);
            pstmt.setString(i++, toothNo);
            pstmt.setString(i++, price);
            pstmt.setString(i++, id);
            
            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(Exception e)
            {
               e.printStackTrace();  
            }
            
         return result;
    }
    
    
}
